﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Groups
{
   public class GroupsDAL
    {
       public static int AddGroups( GroupsEntity group)
       {
           Database db = DatabaseFactory.CreateDatabase();
           StringBuilder InsertGroup = new StringBuilder();

           #region 
           InsertGroup.Append(@"INSERT INTO [Groups] 
				([ShopID],[g_Title],[g_Price],[g_Discount],[g_foucsmap],[g_SigupTotal],[g_endingTime],[g_CTime],[g_Content],[procTypeID],[IsEnable])
				 VALUES (@ShopID,@g_Title,@g_Price,@g_Discount,@g_foucsmap,@g_SigupTotal,@g_endingTime,@g_CTime,@g_Content,@procTypeID,@IsEnable);
                ");

           DbCommand InsertMemberCommand = db.GetSqlStringCommand(InsertGroup.ToString());
           
           db.AddInParameter(InsertMemberCommand, "@ShopID", DbType.Int32, group.ShopID);
           db.AddInParameter(InsertMemberCommand, "@g_Title", DbType.String, group.Title);
           db.AddInParameter(InsertMemberCommand, "@g_Price", DbType.Decimal, group.Price);
           db.AddInParameter(InsertMemberCommand, "@g_Discount", DbType.Double, group.Discount);
           db.AddInParameter(InsertMemberCommand, "@g_foucsmap", DbType.String, group.Foucsmap);
           db.AddInParameter(InsertMemberCommand, "@g_SigupTotal", DbType.Int32, 0);
           db.AddInParameter(InsertMemberCommand, "@g_endingTime", DbType.DateTime, group.EndingTime);
           db.AddInParameter(InsertMemberCommand, "@g_CTime", DbType.DateTime, group.CTime);
           db.AddInParameter(InsertMemberCommand, "@g_Content", DbType.String, group.Content);
           db.AddInParameter(InsertMemberCommand, "@procTypeID", DbType.Int32, group.ProcTypeID);
           db.AddInParameter(InsertMemberCommand, "@IsEnable", DbType.Boolean, group.isEnable);

           return db.ExecuteNonQuery( InsertMemberCommand);
           #endregion
       }

       /// <summary>
       /// 查出分页记录总条件
       /// </summary>
       /// <param name="categorid"></param>
       /// <param name="shopid"></param>
       /// <returns></returns>
       public static int GroupCount(int categorid, int shopid, int AreaID)
       {
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand SelectMemberCommand = null;
           string seleCount = @"SELECT  *,ROW_NUMBER() OVER (order BY groupid desc) as sequence  
                                FROM Groups where isEnable=1 ";
           if (categorid != 0)
           {
               seleCount += " and procTypeID=@procTypeID";
           }
           if (shopid != 0)
           {
               seleCount += " and ShopID=@ShopID";
           }
           //区域ID
           if (AreaID != 0)
           {
               seleCount += " and shopid in(select shopid from Shop where AreaID=@AreaID)";
           }

           SelectMemberCommand = db.GetSqlStringCommand(seleCount);
           //商家ID
           if (shopid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@ShopID", DbType.Int32, shopid);
           }
           //分类ID
           if (categorid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@procTypeID", DbType.Int32, categorid);
           }
           //区域ID
           if (AreaID != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@AreaID", DbType.Int32, AreaID);
           }

           DataSet ds=db.ExecuteDataSet(SelectMemberCommand);
           if(ds.Tables[0]!=null && (ds.Tables[0].Rows.Count>0))
               return ds.Tables[0].Rows.Count;
           return 0;

       }

       public static List<GroupsEntity> GetGroups(int startRecordIndex, int endRecordIndex, int categorid,int shopid,int AreaID)
       {
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand SelectMemberCommand = null;
           List<GroupsEntity> groupEntity = new List<GroupsEntity>();

           string SelectMember = @"select * from (SELECT  *,ROW_NUMBER() OVER (order BY groupid desc) as sequence  
                                FROM Groups   ) t WHERE isEnable=1 and sequence>=" + startRecordIndex + " and sequence<=" + endRecordIndex;
          //分类ID
           if (categorid != 0)
           {
               SelectMember += " and procTypeID=@procTypeID";
           }
           //商家ID
           if (shopid != 0)
           {
               SelectMember += " and ShopID=@ShopID";
           }
           //区域ID
           if (AreaID != 0)
           {
               SelectMember += " and shopid in(select shopid from Shop where AreaID=@AreaID)";
           }


           SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
           //商家ID
           if (shopid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@ShopID", DbType.Int32, shopid);
           }
           //分类ID
           if (categorid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@procTypeID", DbType.Int32, categorid);
           }
           //区域ID
           if (AreaID != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@AreaID", DbType.Int32, AreaID);
           }

           using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
           {
               while (dr.Read())
               {
                   GroupsEntity group = GroupEntity(dr);
                   groupEntity.Add(group);
               }
           }

           return groupEntity;
       }

       public static List<GroupsEntity> GetGroups(string search,int shopid,int categorid)
       {
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand SelectMemberCommand = null;
           List<GroupsEntity> groupEntity = new List<GroupsEntity>();

           string SelectMember = @"SELECT  [groupID],[ShopID],[g_Title],[g_Price],[g_Discount],[g_foucsmap],[g_SigupTotal],[g_endingTime],[g_CTime]
                                 ,[g_Content],[procTypeID],[IsEnable] FROM Groups WHERE 1=1 ";

           if (!string.IsNullOrEmpty(search))
               SelectMember += " and g_Title like '%" + search + "%'";

           if (shopid != 0)
           {
               SelectMember += " and ShopID=@ShopID";
           }
           if (categorid != 0)
           {
               SelectMember += " and procTypeID=@procTypeID";
           }
           SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
           //商家ID
           if (shopid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@ShopID", DbType.Int32, shopid);
           }
           //分类ID
           if (categorid != 0)
           {
               db.AddInParameter(SelectMemberCommand, "@procTypeID", DbType.Int32, categorid);
           }

           using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
           {
               while (dr.Read())
               {
                   GroupsEntity group = GroupEntity(dr);
                   groupEntity.Add(group);
               }
           }

           return groupEntity;
       }

       public static GroupsEntity GetGroups(int groupid)
       {
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand SelectMemberCommand = null;
           GroupsEntity group = null;
           string SelectMember = @"SELECT [groupID],[ShopID],[g_Title],[g_Price],[g_Discount],[g_foucsmap],[g_SigupTotal],[g_endingTime],[g_CTime]
                                 ,[g_Content],[procTypeID],[IsEnable] FROM Groups WHERE groupID=@groupID ";

          
           SelectMemberCommand = db.GetSqlStringCommand(SelectMember);
           db.AddInParameter(SelectMemberCommand, "@groupID", DbType.Int32, groupid);

           using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
           {
               while (dr.Read())
               {
                    group = GroupEntity(dr);
                   
               }
           }

           return group;
       }
       public static int DelGroup(int groupid)
       {
            Database db = DatabaseFactory.CreateDatabase();

           string DeleteGroup = @"
            DELETE FROM [Groups] WHERE groupID=@groupID";
           DbCommand delgroupCommand = db.GetSqlStringCommand(DeleteGroup);
           db.AddInParameter(delgroupCommand, "@groupID", DbType.Int32, groupid);
           return db.ExecuteNonQuery(delgroupCommand);
       }

       private static GroupsEntity GroupEntity(IDataReader dr)
       {
           GroupsEntity group = new GroupsEntity();

           group.GroupID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
           group.ShopID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
           group.Title = dr.IsDBNull(2) ?string.Empty : dr.GetString(2);
           group.Price = dr.GetDecimal(3);
           group.Discount =float.Parse(dr.GetDouble(4).ToString());
           group.Foucsmap = dr.GetString(5);
           group.SigupTotal = dr.GetInt32(6);
           group.EndingTime = dr.GetDateTime(7);
           group.CTime = dr.GetDateTime(8);
           group.Content =dr.GetString(9);
           group.ProcTypeID = dr.GetInt32(10);
           group.isEnable = dr.GetBoolean(11);

           return group;
       }

       public static int UpdateGroup(GroupsEntity group)
       {
           string strsql = @"UPDATE [casemeet.com].[dbo].[Groups]
                           SET [ShopID] =@ShopID
                              ,[g_Title] = @g_Title
                              ,[g_Price] = @g_Price
                              ,[g_Discount] =@g_Discount
                              ,[g_foucsmap] =@g_foucsmap
                              ,[g_SigupTotal] = @g_SigupTotal
                              ,[g_endingTime] = @g_endingTime
                              ,[g_Content] =@g_Content
                              ,[procTypeID] =@procTypeID
                              ,[IsEnable] = @IsEnable
                         WHERE groupID=@groupID";

           Database db = DatabaseFactory.CreateDatabase();
           DbCommand updateCommand = db.GetSqlStringCommand(strsql);

           db.AddInParameter(updateCommand, "@ShopID", DbType.Int32, group.ShopID);
           db.AddInParameter(updateCommand, "@g_Title", DbType.String, group.Title);
           db.AddInParameter(updateCommand, "@g_Price", DbType.Decimal, group.Price);
           db.AddInParameter(updateCommand, "@g_Discount", DbType.Double, group.Discount);
           db.AddInParameter(updateCommand, "@g_foucsmap", DbType.String, group.Foucsmap);
           db.AddInParameter(updateCommand, "@g_SigupTotal", DbType.Int32, 0);
           db.AddInParameter(updateCommand, "@g_endingTime", DbType.DateTime, group.EndingTime);
           db.AddInParameter(updateCommand, "@g_CTime", DbType.DateTime, group.CTime);
           db.AddInParameter(updateCommand, "@g_Content", DbType.String, group.Content);
           db.AddInParameter(updateCommand, "@procTypeID", DbType.Int32, group.ProcTypeID);
           db.AddInParameter(updateCommand, "@IsEnable", DbType.Boolean, group.isEnable);
           db.AddInParameter(updateCommand, "@groupID", DbType.Int32, group.GroupID);
           return db.ExecuteNonQuery(updateCommand);


       }
    }
}
